package com.cy.pj.notice.dao;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository //此注解一般用于描述数据访问对象的具体实现类
public class NoticeJdbcDaoimpl接口实现类 implements NoticeJdbcDao{
    @Autowired
    private DataSource dataSource;
    @Override
    public int insertNotice(Map<String, Object> map) {
        return 0;
    }

    @Override
    public List<Map<String, Object>> selectNotices() throws SQLException {
        //1,获取连接
            Connection conn=dataSource.getConnection();
            //2,创建PreparedStatement
            //***注意的教训：要在from前面加个“空格”，和上一句隔开来，不然会报错
            String sql="select id,title,content,status,type,createdTime"+
                    " from sys_notices where id>=?";
            PreparedStatement pstmt=conn.prepareStatement(sql);
            //3，发送sql语句，并返回结果集
            pstmt.setInt(1,2);
            //ResultSet rs=pstmt.execute();
            boolean flag=pstmt.execute();
            ResultSet rs=null;
        List<Map<String,Object>> list=new ArrayList<>();
            if(flag){
                rs=pstmt.getResultSet(); //返回的查询结果集
                ResultSetMetaData rsmd=rs.getMetaData();//获取结果集对应的的元数据--用来获取字段名和表名
                int columnCount=rsmd.getColumnCount(); //结果集有几列--6列(id,title,content,status,type,createdTime)

                while(rs.next()){    //行映射，循环一次取一行，一行记录一个map对象
                    Map<String,Object> map=new HashMap<>();
                    for(int i=2;i<=columnCount;i++){  //循环一次取一行结果的值
                        String b=rsmd.getColumnLabel(i);//for循环取每列的--字段别名(没有别名就是默认的别名)
                        map.put(b,rs.getObject(b));
                    }
                /*map.put("id",rs.getInt("id"));
                map.put("title",rs.getString("title"));
                map.put("content",rs.getString("content"));
                map.put("status",rs.getString("status"));
                map.put("type",rs.getString("type"));
                map.put("createdTime",rs.getTimestamp("createdTime"));
                */
                    list.add(map);
                }
            }
            rs.close();
            pstmt.close();
            conn.close();
            return list;

    }
    }
